<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/4/14 0014
 * Time: 上午 10:14
 */

namespace Admin\Controller;


use Think\Controller;

class StatisticalLogisticsController extends Controller
{


    public $local_id;

    public function __construct()
    {
        parent::__construct();

        $this->local_id = session('userInfo')['local_id'];
    }

    /**
     *物流统计
     */
    public function StaticalSet()
    {
        $b_time = $_GET['start_time'];
        $end_time = $_GET['end_time'];
        if ($_GET['start_time'] != '开始时间' && $_GET['end_time'] != '结束时间' && !empty($_GET['end_time']) && !empty($_GET['start_time'])) {
            $where = "local_id = " . $this->local_id . " and day >= '{$b_time}' and day <= '{$end_time}'";
        } else {
            $where = "local_id = " . $this->local_id;
        }
        $count = M('statistical_logistics')->where($where)->count();// 查询满足要求的总记录数
        $Page = new \Think\Page($count, 15);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show = $Page->show();// 分页显示输出

        $rows= M('statistical_logistics')->where($where)->limit($Page->firstRow . ',' . $Page->listRows)->order('day desc')->select();
        $this->assign('re_search', $rows);
        $this->assign('page', $show);
        $this->display('logistic');

    }


    // 定时任务  每天统计前一天数据
    public function setone()
    {
        $name = M('webpos_local_store')->field('local_id')->select();
        foreach($name as $v)
        {
          $this->set1($v['local_id']);
        }
    }
    //一次性统计所有
    public function setall()
    {
        $name = M('webpos_local_store')->field('local_id')->select();
        foreach($name as $v)
        {
            $this->set2($v['local_id']);
        }
    }

    /**
     * 每天插入一条
     */
    public function set1($local_id)
    {

        $b_time = strtotime(date('Y-m-d',time()-86400));
        $end_time = strtotime(date('Y-m-d',time()));
        $where = "a.disabled = 'false' and a.local_id = " .$local_id . " and a.t_begin >= " . $b_time . " and a.t_begin <= " . $end_time;

        $where1 = "pay_status = '1' and local_id = " . $local_id;
        $res_search = M()->query("select FROM_UNIXTIME(a.t_begin,\"%Y-%m-%d\") as day,count(*)as count from sdb_b2c_delivery as a  where " . $where . "  GROUP BY  day ORDER BY day desc ");
        // 总金额
        $res_search0 = M()->query("select FROM_UNIXTIME(a.t_begin,\"%Y-%m-%d\") as day,count(*)as count ,order_id from sdb_b2c_delivery as a  where " . $where . "  GROUP BY  order_id ORDER BY day desc ");
        //速配
        $res_search1 = $this->getRes(1, $where);
        // 快递
        $res_search2 = $this->getRes(2, $where);
        //自提
        $res_search3 = $this->getRes(3, $where);

        $list1 = $this->getMoney($res_search1, $where1);  //速配
        $list2 = $this->getMoney($res_search2, $where1);  // 快递
        $list3 = $this->getMoney($res_search3, $where1);  //自提
        $listmoney = $this->getMoney($res_search0, $where1);  //总金额
        //计算金额
        $ziti_money = $this->money($list3);
        $kuaidi_money = $this->money($list2);
        $supei_money = $this->money($list1);
        $money = $this->money($listmoney);
// 分别统计赋值

        for ($i = 0; $i <= count($res_search); $i++) {
            foreach ($res_search as &$b) {
                if ($b['day'] == array_keys($list3)[$i]) {
                    $b['ziti_num'] = count($list3[$b['day']]);
                    $b['ziti_money'] = $ziti_money[$b['day']];
                }
                if ($b['day'] == array_keys($list1)[$i]) {
                    $b['supei_num'] = count($list1[$b['day']]);
                    $b['supei_money'] = $supei_money[$b['day']];
                }
                if ($b['day'] == array_keys($list2)[$i]) {
                    $b['kuaidi_num'] = count($list2[$b['day']]);
                    $b['kuaidi_money'] = $kuaidi_money[$b['day']];
                }
                // 总金额
                if ($b['day'] == array_keys($money)[$i]) {
                    $b['money'] = $money[$b['day']];
                }
                $b['local_id'] = $local_id;
                $b['created_at'] = date("Y-m-d H:i:s");
            }
        }

        foreach($res_search as $h)
        {
            $data=[];
            $data['name'] ="物流统计";
            $data['run_time'] =time();
            $data['run_msg']='日期:'.$h["day"].',门店ID:'.$local_id.'';
            $rows = M('statistical_logistics')->where(['day'=>$h['day'],'local_id' => $local_id])->select();
            if($rows){
                M('statistical_logistics')->where(['day'=>$h['day'],'local_id' => $local_id])->delete();
            }
            $result = M('statistical_logistics')->add($h);
            if (!$result) {
                $data['log_status']=0;
            }
            M('statistical_log')->add($data);


        }
    }





    /**
     * 统计所有并插入数据表
     */
    public function set2($local_id)
    {
        $b_time = strtotime($_GET['start_time']);
        $end_time = strtotime($_GET['end_time']);
        if ($_GET['start_time'] != '开始时间' && $_GET['end_time'] != '结束时间' && !empty($_GET['end_time']) && !empty($_GET['start_time'])) {
            $where = "a.disabled = 'false' and a.local_id = " . $local_id . " and a.t_begin >= " . $b_time . " and a.t_begin <= " . $end_time;
        } else {
            $where = "a.disabled = 'false' and a.local_id = " . $local_id;
        }
        $where1 = "local_id = " . $local_id;
        $res_search = M()->query("select FROM_UNIXTIME(a.t_begin,\"%Y-%m-%d\") as day,count(*)as count from sdb_b2c_delivery as a  where " . $where . "  GROUP BY  day ORDER BY day desc ");
        // 总金额
        $res_search0 = M()->query("select FROM_UNIXTIME(a.t_begin,\"%Y-%m-%d\") as day,count(*)as count ,order_id from sdb_b2c_delivery as a  where " . $where . "  GROUP BY  order_id ORDER BY day desc ");
        //速配
        $res_search1 = $this->getRes(1, $where);
        // 快递
        $res_search2 = $this->getRes(2, $where);
        //自提
        $res_search3 = $this->getRes(3, $where);

        $list1 = $this->getMoney($res_search1, $where1);  //速配
        $list2 = $this->getMoney($res_search2, $where1);  // 快递
        $list3 = $this->getMoney($res_search3, $where1);  //自提
        $listmoney = $this->getMoney($res_search0, $where1);  //总金额
        //计算金额
        $ziti_money = $this->money($list3);
        $kuaidi_money = $this->money($list2);
        $supei_money = $this->money($list1);
        $money = $this->money($listmoney);
// 分别统计赋值

        for ($i = 0; $i <= count($res_search); $i++) {
            foreach ($res_search as &$b) {
                if ($b['day'] == array_keys($list3)[$i]) {
                    $b['ziti_num'] = count($list3[$b['day']]);
                    $b['ziti_money'] = $ziti_money[$b['day']];
                }
                if ($b['day'] == array_keys($list1)[$i]) {
                    $b['supei_num'] = count($list1[$b['day']]);
                    $b['supei_money'] = $supei_money[$b['day']];
                }
                if ($b['day'] == array_keys($list2)[$i]) {
                    $b['kuaidi_num'] = count($list2[$b['day']]);
                    $b['kuaidi_money'] = $kuaidi_money[$b['day']];
                }
                // 总金额
                if ($b['day'] == array_keys($money)[$i]) {
                    $b['money'] = $money[$b['day']];
                }
                $b['local_id'] = $local_id;
                $b['created_at'] = date("Y-m-d H:i:s");
            }
        }
        foreach($res_search as $h)
        {

            $rows = M('statistical_logistics')->where(['day'=>$h['day'],'local_id' => $local_id])->select();
            if($rows)
            {
                exit();
            }
            M('statistical_logistics')->add($h);
        }
    }


    // 按发货类型查询数据
    public function getRes($num = '', $where)
    {

        $res_search = M()->query("select FROM_UNIXTIME(a.t_begin,\"%Y-%m-%d\") as day,count(*)as count ,order_id from sdb_b2c_delivery as a  where " . $where . " and delivery='" . $num . "' GROUP BY  order_id ORDER BY day desc ");
        return $res_search;
    }

    // 按物流订单查询对应的价钱
    public function getMoney($res_search, $where1)
    {


        $res1 = array();
        foreach ($res_search as $k => $v) {
            $res1[$v['day']][] = $v['order_id'];
        }

        $res11 = array();
        foreach ($res1 as $k => $v) {
            $res11[$k][] = implode(',', $v);
        }
        $list = [];
        // 分三种送货方式
        foreach ($res11 as $k => $v) {
            $list[$k] = M()->query("select count(*) as count,sum(total_amount) as money from sdb_b2c_orders where " . $where1 . " and order_id IN ($v[0])  GROUP BY  order_id ");
        }

        return $list;

    }
// 价格
    public function money($listmoney = [])
    {
        //每一天的总金额
        if ($listmoney) {
            $money = [];
            foreach ($listmoney as $k => $v) {
                foreach ($v as $h) {
                    $money[$k] += $h['money'];
                }
            }
            return $money;
        }

    }


    public function __destruct()
    {
        unset($this->local_id);
    }



    /**
     * 生成报表
     *
     */
    public function createReport()
    {
        $b_time =  I('start_time');
        $end_time =I('end_time');
        if ($_GET['start_time'] != '开始时间' && $_GET['end_time'] != '结束时间' && !empty($_GET['end_time']) && !empty($_GET['start_time'])) {
            echo   $where = "local_id = " . $this->local_id . " and day >= '{$b_time}' and day <= '{$end_time}'";
        } else {
            echo  $where = "local_id = " . $this->local_id;
        }
        $field = '`day`,`count`,`supei_num`,`ziti_num`,`kuaidi_num`,`money`,`supei_money`,`kuaidi_money`,`ziti_money`';
        $count = M('statistical_logistics')
            ->where($where)
            ->count();
        if ($count > 0) {
            $list = M('statistical_logistics')
                ->where($where)
                ->order('day desc')
                ->field($field)
                ->select();
        }

        $expCellName = [
            ['0', '日期', 'day'], ['1', '总订单数', 'count'], ['2', '速配', 'supei_num'], ['3', '自提', 'ziti_num'],
            ['4', '快递', 'kuaidi_num'], ['5', '总金额', 'money'], ['6', '速配总金额', 'supei_money'],
            ['7', '快递总金额', 'kuaidi_money'], ['8', '自提总金额', 'ziti_money']
        ];
        $money =array_sum(array_column($list, 'money'));
        $dingdan = array_sum(array_column($list, 'count'));
        $str = $b_time.' 至 '.$end_time.' 共'.$dingdan.'笔订单,总金额'.$money.'元';
        //  var_dump($expCellName, $list, $str);return;
        $this->excel3($expCellName, $list, $str);

    }

    public function excel3($expCellName, $expTableData, $str) {
        $xlsTitle = iconv('utf-8', 'gb2312', '');//文件名称
        $fileName = date('物流统计_Ymd');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        Vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $str);
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        if ($dataNum > 0) {
            //逻辑区
            for ($i = 0; $i < $dataNum; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][2]]);
                }
            }
        }
        ob_end_clean();//清除缓冲区,避免乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


}